package com.easyapi.server.utils;

import com.alibaba.druid.DbType;
import com.alibaba.druid.sql.SQLUtils;
import com.alibaba.druid.sql.ast.SQLStatement;
import com.alibaba.druid.sql.ast.statement.*;
import com.alibaba.druid.sql.dialect.mysql.parser.MySqlStatementParser;
import com.easyapi.common.metadata.SqlParserResult;
import org.apache.commons.collections4.MapUtils;
import org.apache.commons.lang3.StringUtils;

import java.util.*;

/**
 * @author: javadadi
 * @Time: 20:49
 * @ClassName: MySQLUtil
 */
public class MySQLUtil {
    public static SqlParserResult sqlParser(String sql) {
        SqlParserResult sqlParserResult = null;
        if (StringUtils.isEmpty(sql)) {
            return null;
        }
        //格式化sql 语句
        String standardSql = SQLUtils.format(sql, DbType.mysql);
        //判断一下sql 类型
        Integer sqlType = sqlTypeParser(standardSql);
        MySqlStatementParser parser = new MySqlStatementParser(standardSql);
        //select
        if (Objects.equals(sqlType, 1)) {
            SQLStatement sqlStatement = parser.parseSelect();
            SQLSelectStatement sqlSelectStatement = (SQLSelectStatement) sqlStatement;
            SQLSelectQuery query = sqlSelectStatement.getSelect().getQuery();
            //单表
            if (query instanceof SQLSelectQueryBlock) {
                sqlParserResult = queryBlockSqlParser(standardSql, query);
            } else {//联合查询
                sqlParserResult= queryUnionSqlParser(standardSql,query);
            }
        }
        // todo update delete insert
        if (sqlParserResult != null) {
            sqlParserResult.setSql(standardSql);
        }
        return sqlParserResult;
    }


    /**
     * 判断sql 类型
     *
     * @param sql
     * @return
     */
    public static Integer sqlTypeParser(String sql) {
        int res = 0;
        if (SQLUtils.parseSingleMysqlStatement(sql) instanceof SQLSelectStatement) {
            res = 1;
        } else if (SQLUtils.parseSingleMysqlStatement(sql) instanceof SQLInsertStatement) {
            res = 2;
        } else if (SQLUtils.parseSingleMysqlStatement(sql) instanceof SQLUpdateStatement) {
            res = 3;
        } else if (SQLUtils.parseSingleMysqlStatement(sql) instanceof SQLDeleteStatement) {
            res = 4;
        }
        return res;
    }

    /**
     * 单表解析--目标表
     *
     * @param sql
     * @param query
     */
    public static SqlParserResult queryBlockSqlParser(String sql, SQLSelectQuery query) {
        SqlParserResult result = new SqlParserResult();
        SQLSelectQueryBlock block = (SQLSelectQueryBlock) query;
        SQLTableSource sqlTableSource = block.getFrom();
        //列名处理
        Map<String, String> columnMap = new HashMap<>();
        List<SQLSelectItem> selectList = block.getSelectList();
        for (SQLSelectItem sqlSelectItem : selectList) {
            String column = sqlSelectItem.getExpr().toString();
            String alias = sqlSelectItem.getAlias();
            columnMap.put(column, alias);
        }
        if (MapUtils.isEmpty(columnMap)) {
            SQLSelectItem sqlSelectItem = selectList.get(0);
            String column = sqlSelectItem.getExpr().toString();
            if (Objects.equals(column, "*")) {
                result.setAllColumns(true);
            }
        }
        result.setColumnMap(columnMap);
        //SQLTableSource 处理--单表查询
        if (sqlTableSource instanceof SQLExprTableSource) {
            SQLExprTableSource sqlExprTableSource = (SQLExprTableSource) sqlTableSource;
            result.setTableName(sqlExprTableSource.getExpr().toString());
            result.setAlias(sqlExprTableSource.getAlias());

        } else if (sqlTableSource instanceof SQLJoinTableSource) { //连接查询
            SQLJoinTableSource sqlJoinTableSource = (SQLJoinTableSource) sqlTableSource;
            //主表
            SQLExprTableSource left = (SQLExprTableSource) sqlJoinTableSource.getLeft();
            result.setTableName(left.getExpr().toString());
            result.setAlias(left.getAlias());
            // right表
            SQLExprTableSource right = (SQLExprTableSource) sqlJoinTableSource.getRight();
            result.setRightTableName(right.getExpr().toString());
            result.setRightAlias(right.getAlias());
        } else if (sqlTableSource instanceof SQLSubqueryTableSource) {
            // 获取子查询的 SQLSelect 对象
            SQLSubqueryTableSource subqueryTableSource = (SQLSubqueryTableSource) sqlTableSource;
            SQLSelectQuery subQuery = subqueryTableSource.getSelect().getQuery();
            // todo 适配子查询为多表情况
            if (subQuery instanceof SQLSelectQueryBlock) {
                SqlParserResult queried = queryBlockSqlParser(subqueryTableSource.getSelect().toString(), subQuery);
                result.setTableName(queried.getTableName());
                result.setAlias(queried.getAlias());
            }
        }//todo 适配其他情况
        return result;
    }

    /**
     * 联合查询
     *
     * @param sql
     * @param query
     * @return
     */
    public static SqlParserResult queryUnionSqlParser(String sql, SQLSelectQuery query) {
        SqlParserResult sqlParserResult = new SqlParserResult();
        SQLUnionQuery sqlUnionQuery = (SQLUnionQuery) query;
        //分别获取左右子查询
        //左子查询
        SQLSelectQuery left = sqlUnionQuery.getLeft();
        SqlParserResult leftResult = queryBlockSqlParser(left.toString(), left);
        //右子查询
        SQLSelectQuery right = sqlUnionQuery.getRight();
        SqlParserResult rightResult = queryBlockSqlParser(right.toString(), right);
        sqlParserResult.setTableName(leftResult.getTableName());
        sqlParserResult.setRightTableName(rightResult.getTableName());
        sqlParserResult.setColumnMap(leftResult.getColumnMap());
        sqlParserResult.setAllColumns(leftResult.isAllColumns());
        return sqlParserResult;
    }

}
